package connection;

import java.sql.*;

public class DbConnection {
	public static String driver;
	public static String url;
	public static String user;
	public static String password;
	public static Connection connection;
	public static Statement statement;
	public ResultSet resultSet;
	
	// Connecting
	static{
		try {
			
			driver = "oracle.jdbc.driver.OracleDriver";
			/*   ---  For connection to server on campus ---
		 	 *	url = "jdbc:oracle:thin:@oracle.students.itee.uq.edu.au:1521:iteeo";
			 *  user = "CSSE3004GE";
			 *  password = "967431";
			 * --------------------------------------------
			 *  --- For connection on personal computer ---
			 * url = "jdbc:oracle:thin:@localhost:1521:XE";
			 * user = "*username*";
			 * password = "*password*";
			 * --------------------------------------------
			 * */
			url = "jdbc:oracle:thin:@oracle.students.itee.uq.edu.au:1521:iteeo";
			user = "CSSE3004GE";
			password = "967431";
			if(user=="REPLACEME" || password == "REPLACEME"){
				System.out.println("*******DON'T FORGET TO REPLACE YOUR ORACLE USERNAME ");
				System.out.println("*******AND PASSWORD IN THE DbConnection CLASS");
			}
			
			Class.forName(driver);
			connection = DriverManager.getConnection(url,user,password);
			System.out.println("---Connection made---");
		} catch (ClassNotFoundException classnotfoundexception) {
			classnotfoundexception.printStackTrace();
			System.err.println("db: " + classnotfoundexception.getMessage());
		} catch (SQLException sqlexception) {
			System.err.println("db.getconn(): " + sqlexception.getMessage());
		}
	}
	
	public void DBConn() {
		DbConnection.connection = this.getConn();
	}
	
	public Connection getConn() {
		return DbConnection.connection;
	}
	
	public void doInsert (String sql) {
		try {
			statement = connection.createStatement();
			int i = statement.executeUpdate(sql);
		} catch (SQLException sqlexception) {
			System.err.println("db.executeInsert: " + sqlexception.getMessage());
		} finally {}
	}
	
	public void doUpdate (String sql) {
		try {
			statement = connection.prepareStatement(sql);
			int i = statement.executeUpdate(sql);
		} catch (SQLException sqlexception) {
			System.err.println("db.executeUpdate " + sqlexception.getMessage());
		}
	}
	
	public void doDelete (String sql) {
		try {
			statement = connection.prepareStatement(sql);
			int i = statement.executeUpdate(sql);
		} catch (SQLException sqlexception) {
			System.err.println("db.executeDelete "+ sqlexception.getMessage());
		}
	}
	
	public ResultSet doSelect (String sql) {
		try {
			connection = DriverManager.getConnection(url,user,password);
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery(sql);
			System.out.println("---Done query---");
		} catch (SQLException sqlexception) {
			System.err.println("db.executeQuery: " + sqlexception.getMessage());
		}
		return resultSet;
	}
	
	public void close (ResultSet resultSet) throws SQLException, Exception {
		if (resultSet != null) {
			resultSet.close();
			resultSet = null;
		}
		
		if (statement != null) {
			statement.close();
			statement = null;
		}
		
		if (connection != null) {
			connection.close();
			connection = null;
		}
		
	}
	
	public void close () throws SQLException, Exception {
		if (statement != null) {
			statement.close();
			statement = null;
		}
		
		if (connection != null) {
			connection.close();
			connection = DriverManager.getConnection(url,user,password);
		}	
	}
}
